Macro Language Functions

Total Functions: 460

Section	How Many
General	8
Variable	8
Logical	17
String	32
Numeric	30
Finance	14
Date and Time	35
Data	34
Data Type	13
Summary Aggregates	19
List and Table	56
Document and JSON	18
Conversion and Formatting	11
Color Support	12
Image Support	6
Template Processing	28
Matrix	7
User Profile	2
User Interaction	12
Geographic	21
Web	8
Catalog	29
Configuration	10
Miscellaneous	7
MongoDB	10
Other Database Support	4

The availability of functions varies by Qarbine edition, release and other factors.

General

baseFileUrl
string = baseFileUrl(subPath1 [, subPathN...] )
Return the base file URL for images etc.
One or more strings may be concatenated together.

accessHostUrl
string = accessHostUrl(anyPortAndSubPath)
Return the host URL for web content.

computeHostUrl
string = computeHostUrl(anyPortAndSubPath)
Return the host URL for web content along with any string argument.

settings
settings = settings( )
Return the list of end user settings.
Settings: [ {name, condition, entries [ {expression, isReadOnly} ] } ]

hostEnvironment
boolean = hostEnvironment(envAlias)
Get where we are running: qarbine, excel, etc.

isHostEnvironment
boolean = isHostEnvironment(envAlias)
Use this to see if running regular Qarbine, as Excel Add-in, as Tableau plug-in etc.

env
string = env(aName)
For settings use only. Returns an environment variable given its name.

errorValue
string = errorValue()
Return the error value constant.

Variable

isVariableDefined
boolean = isVariableDefined(varName 1 [,varName n] )
Return false if any variable in the list is not defined.

isVariableReadOnly
boolean = isVariableReadOnly(varName 1 [,varName n] )
Return false if any variables in the list are not read only

resultVariable
name = resultVariable(formula)
Return the result variable of the given formula, 'resultVariable = expression'.

scanForVariables
data = scanForVariables(string)
Scan the string for block evaluations [! ... !] and @variable replacements.

scanFileForVariables
data = scanFileForVariables(fullStoragePath | folderPath, name)
scanFileForVariables("q_catalog|qf_general/foo/bar", "A bunch of animals.txt")
scanFileForVariables("q_catalog|qf_general/foo/bar/A bunch of animals.txt")
Scan the contents of the file for block evaluations [! ... !] and @variable replacements.
This is short hand for scanForVariables( fileContent(....) )

setVariableReadOnly
setVariableReadOnly(varName 1 [,varName n] )
Set the variables to be read only.

value
object = value(variableName)
Return the variable with the given name. Useful when names are dynamic or concatenated.

allVariables
nameAndValueList = allVariables( )
Return all of the variables in a list. The elements have name and value fields.

Logical

assert
boolean = assert(value, type [, cancelMessage])
Return boolean of whether the value is the given type.

if
value = if(expression, useThisVaue, elseUseThisValue)
If the expression is true return the 2nd value otherwise the 3rd value.

ifNot
value = ifNot(expression, useThisVaue, elseUseThisValue)
If the expression is false return the 2nd value otherwise the 3rd value.

ifNull
value = ifNull(testValue, useThisVaue, elseUseThisValue)
If the expression is null return the 2nd value otherwise the 3rd value.
Convenience method for less verbose expressions.

ifNotNull
value = ifNotNull(testValue, useThisVaue, elseUseThisValue)
If the expression is not null return the 2nd value otherwise the 3rd value.
Convenience method for less verbose expressions.

isEmpty
boolean = isEmpty(value [, value n] )
Return true if ALL of the values are either null, an empty Array or its string equivalent has length 0.

isNotEmpty
boolean = isNotEmpty(value [, value n] )
Return true if the values are either not null, an Array with length > 0 or its string equivalent has length > 0.

true
boolean = true()
Return the boolean true value.

false
boolean = false()
Return the boolean false value.

and
boolean = and(booleanExpressionList)
Return true if all expressions are true. Stop at FIRST false! If there are no arguments then return false.

or
boolean = or(booleanExpressionList)
Stop at FIRST true! If there are no true arguments then return false.

not
boolean = not(booleanExpression)
Boolean negates the expression value. Return false if there is no argument.

notBetween
boolean = notBetween(value, low, high, lowValueInclusive, highValueInclusive)
Is the value <|<= low or >|>= high

between
boolean = between(value, low, high, lowValueInclusive, highValueInclusive)
Is the value >|>= low and <|<= high

compare
integer = compare(string1, string2 [, locale [, caseInsensitive] ] )
Compare 2 strings. Return -1 when string1 < string2. Return 0 if equal. Return 1 otherwise.

compareValue
result = compareValue(value1, value2, lessThanResult, equalResult, greaterThanResult, caseInsensitive)
Compare 2 values. Return lessThanResult when string1 < string2.
Return equalResult if equal.
Return greaterThanResult otherwise.
The default caseInsensitive is false and is only used with strings.
This means the string comparison with CC less than bb.
Upper case ASCII is less than lower case ASCII.

switch
value = switch( expression, value1, result1, value2, result2, ... value_n, result_n [, default] )
If there are no matching values, and no default argument is supplied, the SWITCH function returns the #N/A! error.

String

asc
number = asc(string)
Returns the ASCII value of a character or the first character in a string.

asText
string = asText( separator, aList [, aList 2+] )
Return the lists concatenated into a single string with the given separator.
The separator is a string. Recognized keywords are comma, tab, and newLine.

char
string = char(numberOrAlias)
Returns the character based on the ASCII value.
Use alias for some common Greek letters and math symbols.
alpha, beta, delta gamma, omega, infinity, pie, sigma, and lambda.

concat
string = concat( string 1 [, string n] )
Concatenate the values into a single string.
A null value is deemed an empty string.

concatFields
string = concatFields(object [,elementFields] )
Concatenate the fields of the given object into a single string.
A null value is deemed an empty string.

indent
string = indent(howMany, string)
Prefix the string with the given number of spaces.
Shortcut instead of concat(pad(#), #value)

indexOf
integer = indexOf(string, findString [, startingIndexBaseOne] )
Return the base one index of the findString in the given string.

lastIndexOf
integer = lastIndexOf(string, findString [, startingIndexBaseOne] )
Return the last base one index of the findString in the given string.

left
string = left(stringOrList, howMany [, stringSuffix] )
If the value is a string then return the first howMany characters.
If the value is an array then return the first howMany elements.
Otherwise JSON'ize the value and apply the string rule above.
For strings, if stringSuffix is defined then it is appended when the string is truncated.

len
integer = len(stringOrList)
Return the length of the string or array.
If the argument is neither then JSON'ize the value and get the length of that.

lower
string = lower(string)
Return the lower case of the given string.

mid
string = mid(string, startBaseOne, howMany)
Return the range of characters of the given string.

pad
string = pad(string, toLength, [padCharacter, [left|right] ] )
Pad the string to the length using the padding character.

quote
string = quote(value, singleQuoteFlag, unquoteVariableFlag)
Quote the value.
The singleQuoteFlag indicates if single quotes should enclose the string.
The unquoteVariableFlag indicates when the value replaces a variable that that value should be single quoted.

repeat
string = repeat(string, howMany)
Repeat the string howMany times.

removeHtmlTags
string = removeHtmlTags(string)
Remove the <...> content from the string.

replace
string = replace(string, oldText, newText [, allFlag [, caseInsensitiveFlag] ] )
Replace occurrences of oldText with newText in the string.

right
string = right(stringOrList, howMany [,stringPrefix] )
If the value is a string then return the last howMany characters.
If the value is an array then return the last howMany elements.
Otherwise JSON'ize the value and apply the string rule above.
For strings, if stringPrefix is defined then it is prepended when the string is truncated.

startsWith
boolean = startsWith(string, matchString)
Return whether the string starts with the matchString.

endsWith
boolean = endsWith(string, matchString)
Return whether the string ends with the matchString.

spaces
string = spaces(howMany)
Return a string with howMany spaces.

split
list = split(string, delimiter)
Crate a list from the string splitting it up at the delimiter.

join
string = join(list, filler)
Join the list elements using the filler.

string
string = string(value)
Return the value as a string.

removeTags
string = removeTags(string)
Remove tags such as '<..>' from the string argument.

substring
string = substring(string, startBaseOne, endBaseOne)
Return a portion of the string.

trim
string = trim(string, atCharacter)
Return a portion of the string using atCharacter as the stopping point.

upperCaseFirstLetter
string = upperCaseFirstLetter( string [, allWords] )
Upper case the first letter of space delimited words.
The allWords default is false.

uncamelize
string = uncamelize( string [, separator [,upperCaseFirstOption]] )
Default separator is _ if none has been provided.
Default upperCaseFirstOption is null.
The upperCaseFirstOption is either:
null - leave alone
true - make upper case
false - make lower case
Back to back capitals remain together (isIRA -> is IRA)

unquote
string = unquote(value)
Useful when we have block replacement [!...!] in a query and do not want the string double quoted upon replacement.

encoded
string = encoded(value [,unquoted] )
Return the URI encoding of the argument. Optionally unquote it.

upper
string = upper(string)
Return the upper case of the given string.

Numeric

abs
number = abs(aNumber)
Return the absolute value of the number.

bigNumber
number = bigNumber(aString)
Convert the value into a BigNumber.

decimal
decimal = decimal(value)
Used to indicate decimal math evaluation. Convert the value into a BigNumber.

decrement
variable = decrement(variable [,initialValue])
Decrement the variable value with automatic initialization to 0 or the second argument.
If the first argument is the NAME of a variable (a String) then assign the new value.
There is no need for the resultVariable portion of the formula.

increment
variable = increment(variable [,initialValue])
Increment the variable value with automatic initialization to 0 or the second argument.
If the first argument is the NAME of a variable (a String) then assign the new value.
There is no need for the resultVariable portion of the formula.

int
integer = int(string)
Convert the string into an integer.

isOdd
boolean = isOdd(aNumber)
Is the number odd?

isEven
boolean = isEven(aNumber)
Is the number even?

mod
number = mod(aNumber, aDivisor)
Return the remainder of aNumber divided by aDivisor

movingAverage
number = movingAverage(expression, howManyBack)
More a convenience function. Have a body cell.

resetMovingAverage
resetIt = resetMovingAverage(resultName)
Reset the named moving average.

negate
number = negate(number)
Negate the number.
A null or NaN value returns 0.

number
number = number(string)
Convert the string into a number.
A null or NaN value returns 0.

numberDecimal
decimal = numberDecimal(string)
Convert the string into a number.
A null or NaN value returns 0.

decimal128LowHigh
decimal = decimal128LowHigh(low, high)
Create a JavaScript number from the Decimal128 low and high values.
See https://en.wikipedia.org/wiki/Decimal128_floating-point_format.
Any null low or high value returns 0.

pow
number = pow(aNumber, power)
Raise a number to the power.

random
number = random( [min, max] )
Return a random number between 0 and 1. Optionally have the number between the range given.

round
integer = round(value)
Returns the value of a number rounded to the nearest integer.

sqrt
number = sqrt(aNumber)
Return the square root of the number.

trunc
integer = trunc(value)
Returns the integer part of a number by removing any fractional digits.

avgOf
number = avgOf(expr 1 [, expr n] )
What is the average of the arguments?

maxOf
number = maxOf(expr 1 [, expr n] )
What is the maximum value of the arguments?

minOf
number = minOf(expr 1 [, expr n] )
What is the minimum value of the arguments?

medianOf
number = medianOf(expr 1 [, expr n] )
What is the median value of the arguments?

modeOf
value = modeOf(expr 1 [, expr n] )
What is the mode value of the arguments?

sumOf
number = sumOf(expr 1 [, expr n] )
What is the sum of each of the arguments?

stDevOf
number = stDevOf(expr 1 [, expr n] )
What is the sample standard deviation of the arguments?

stDevPOf
number = stDevPOf(expr 1 [, expr n] )
What is the population standard deviation of the arguments?

varOf
number = varOf(expr 1 [, expr n] )
What is the variance of the arguments?

varPOf
number = varPOf(expr 1 [, expr n] )
What is the population variance of the arguments?

Finance

financeAM
number = financeAM(principal, rate, total number of payments, [type])
Amortization is the paying off of debt with a fixed repayment schedule in regular installments over a period of time.1

financeCAGR
number = financeCAGR(beginning value, ending value, number of periods)
Compound Annual Growth Rate (CAGR) is the year-over-year growth rate of an investment over a specified period of time.

financeCI
number = financeCI(rate, compoundings per period, principal, number of periods)
Compound Interest is the interest calculated on the initial principal and also
on the accumulated interest of previous periods of a deposit or loan.

financeDF
number = financeDF(rate, number of periods)
The Discount Factor (DF) is the factor by which a future cash flow must be multiplied in order to obtain the present value.

financeFV
number = financeFV(rate, cash flow, number of periods)
Future Value (FV) is the value of an asset or cash at a specified date in the future that is equivalent in value to a specified sum today

financeIRR
number = financeIRR(initial investment, [cash flows ...])
Internal Rate of Return (IRR) is the discount rate often used in capital budgeting
that makes the net present value of all cash flows from a particular project equal to zero.

financeLR
number = financeLR(total liabilities, total debts, total income)
Leverage Ratio (LR) is used to calculate the financial leverage of a company or individual
to get an idea of the methods of financing or to measure ability to meet financial obligations.

financeNPV
number = financeNPV(rate, initial investment, [cash flows])
Net Present Value (NPV) compares the money received in the future to an amount of money
received today, while accounting for time and interest [through the discount rate].
It's based on the principal of time value of money (TVM), which explains how time affects monetary value.

financePP
number = financePP(number of periods, [cash flows])
Payback Period (PP) is the length of time required to recover the cost of an investment.
number of periods takes a 0 value for even cash flows;
for uneven cash flows, number of periods takes any number of projected periods.

financePV
number = financePV(rate, cash flow)
Present Value (PV) is the current worth of a future sum of money or stream of cash flows given a specified rate of return.

financePI
number = financePI(rate, initial investment, [cash flows])
Profitability Index (PI) is an index that attempts to identify the relationship between
the costs and benefits of a proposed project through the use of a ratio calculated.

financeROI
number = financeROI(initial investment, earnings)
Return on Investment (ROI) is a simple calculation that tells you the bottom line return of any investment.

financeR72
number = financeR72(rate)
Rule of 72 (R72) is a rule stating that in order to find the number of years required
to double your money at a given interest rate, you divide the compound return into 72.

financeWACC
number = financeWACC(market value of equity, market value of debt, cost of equity, cost of debt, tax rate)
Weighted Average Cost of Capital (WACC) is the rate that a company is expected to pay on
average to all its security holders to finance its assets.

Date and Time

currentDayOfMonth
integer = currentDayOfMonth()
Return 2 digit day of month base ONE relative to the USER.

currentMonth
integer = currentMonth( )
Return month number base ONE relative to the USER.

currentWeek
integer = currentWeek( )
Return week number base ONE relative to the USER.

currentQuarter
integer = currentQuarter(quarterStartMonthBaseOne, useUtcDate)
Return the current quarter.

currentYear
integer = currentYear( )
Return the current year as a 4 digit year.

quarterToDateRange
dateList = quarterToDateRange(number, year, quarterStartBaseOne, useUtcDate)
Returns an array with 2 Date elements.

dateDiff
number = dateDiff(date1, date2 [,units [,floatFlag] ] )
The supported measurements are years, months, weeks, days, hours, minutes, seconds, and ms.
The default unit of measure is ms (milliseconds).
The return is a positive number if date1 is before date2.
Logically this is date2 - date1, or date1 + answer = date2.

dateMinus
date = dateMinus(date, number, units[, number, units] )
Return the date minus the other value.

datePlus
date = datePlus(date, number, units [, number, units] )
Return the date plus the other value.

dateToString
string = dateToString(date, template, options...)
Return the date as a string using the given template.

dateUtcProperty
value = dateUtcProperty(date, property [,property...])
Property names: year, month, quarter, day, hour, minute, second, ms, millisecond.

dateProperty
value = dateProperty(date, property [,property...])
Property names: year, month, quarter, day, hour, minute, second, ms, millisecond.

dayName
string = dayName(date or 1To7, shortFlag, mondayIsDay1Flag)
Return the day name of the given date or from the day of the week index (1-7).
The default day of week indices are Sunday as 1 through Saturday as 7.
Specify the mondayIsDay1Flag for Monday as 1 through Sunday as 7.

dayNameAt
string = dayNameAt(indexSundayIsOne, shortFlag, sundayIsDay1Flag)
Return the day name for the day of the week number.
The default day of week indices are Monday as 1 through Sunday as 7 per ISO.
Specify the sundayIsDay1Flag for Sunday as 1 through Saturday as 7.

isLeapYear
boolean = isLeapYear(year)
Return if the year is a leap year.

datefromTimeframe
date = datefromTimeframe(howMany [, units])
The units default is 'seconds'.

isoDate
Date = isoDate(string)
Convert the ISO formatted date string into a date.

shiftDateByTimezoneOffset
Date = shiftDateByTimezoneOffset(date)
Shift the date by the offset from GMT.
this provides for easier timestamp presentation.

utcToLocal
Date = utcToLocal(date)
Convert the UTC formatted date string into a date.
Use this when the data is stored as UTC.

monthName
string = monthName(date or 1To12, shortFlag)
Return the date's month name from the date or the month index (1-12).

newDate
date = newDate(IsoDateString)
date = newDate(milliseconds)
newDate(monthNdxBaseOne, dayNdxBaseOne, year [, hours, minutes, seconds] )
Create a date.
A first value of 0 returns the current date.
A first value more than 12 indicates a milliseconds argument. Otherwise it is the month index base one.

now
date = now( )
Return the current date.

nowMinus
date = nowMinus(number, units [,number, units] )
Return the current date minus the given number of units.

nowPlus
date = nowPlus(number, units [,number, units] )
Return the current date plus the given number of units.

setDateToMidnight
date = setDateToMidnight(date)
Set the date's time portion to midnight.
zero out the time pieces nearest midnight in the past

setDayOfMonth
date = setDayOfMonth(date, dayOfMonthBaseOne | 'first'| 'last')
Return a new date with the day of month adjusted per argument 2.

timezoneOffsetInHours
minutes = timezoneOffsetInHours()
Return user time relative to GMT. IGNORES daylight savings time!
It may be a fraction!

timezoneOffsetInMinutes
minutes = timezoneOffsetInMinutes()
Return user time relative to GMT. IGNORES daylight savings time!

today
date = today( [deltaDays] )
Return today's END date optionally plus or minus the given number of days.

tomorrow
date = tomorrow( [deltaDays] )
Return the date for END of tomorrow optionally plus or minus the given number of days.

yesterday
date = yesterday( [deltaDays] )
Return the date for START of yesterday optionally plus or minus the given number of days.

within
boolean = within(#, units [, #, units] )
An alias for nowMinus(), maps to regEx in QBE world.

year
integer = year(date)
Return the date's year.

Data

classNamed
clazz = classNamed( [JavaScript|language...], className)
Return the given meta class.

dataServiceDriverNames
list = dataServiceDriverNames( )
Return the list of data service driver names.

dataServiceNames
list = dataServiceNames( [onlyDatabaseOnes] , [onlyActiveOnes] )
Return the list of data service names.
For an administrator account, ALL data service names are returned by default regardless of visibility.
Use the 2nd argument to return only the active and visible ones.
Default is all of them.

dataServices
dataServices = dataServices()
This includes Static, Formula, and REST data services.
These are objects with fields including name and driverName.

scanCatalogReferences
list = scanCatalogReferences(allFlag, sortByPath)
Return information on component references.
The allFlag default is false. Only missing references will be returned.
The sortByPath default is false with a sort order of status, folder path, and name.
When sortByPath is true the sort order is folder path, name and status.

logPrincipalNames
list = logPrincipalNames( )
Return a list of principal names in the application log.

accessLevel
string = accessLevel(accessLevelCode)
Use for reports which contain principals.

accessLevelImage
string = accessLevelImage(accessLevelCode)
Use for reports which contain principals.

enabledFor
string = enabledFor(enabledForCode)
Use for reports which contain resources, storage zones, and data services.
The stored enabledFor value is the numeric code.

usage
string = usage(usageCode)
Use for reports which contain principals.

activeDataFetcher
result = activeDataFetcher( )
Return an object with any active dataServiceName and dataSourceName.

retrieveData
result = retrieveData(dataService, query [,database [,maxSize] ])
Retrieve data using the data service and other parameters.

dataSourceData
data = dataSourceData(catalogPath, componentName)
Return data by running the given data source.

emptyCell
value = emptyCell()
Return an empty string. May use this with If(...) for example.

endOfData
boolean = endOfData(overallFlag)
Return if the current data retrieval is at its end of data.

useAsNewDataList
list = useAsNewDataList(list)
Can use in conjunction with gatherData() when some post processing of the retrieved answer set list is wanted.
For example, useAsNewList( listSlice( gatherData(), 3) )
Use this on a group header to replace that group's active list driving its inner sections with this one.
Do NOT use this function with any playback using cache turned on- useAsNewdataList().

gatherData
list = gatherData(useAsNewList)
Obtain all of the data from the current data source.

getNext
value = getNext()
Return the next element from the active data retrieval.

getNextAfterEvaluation
value = getNextAfterEvaluation( expression )
Return the next element from the active data retrieval after evaluating the expression.
After the current cell obtains its results, a call is made to get the next data element.
This allows the next cell to then do anything with the just fetched data element.
You can have several of these on the same line as a way to traverse the answer set across the line rather
than waiting until the end of the section to read the next element.
At the end of the section a call to get the next element will be made.
So, do not have the last cell on the line have getNextAfterEvaluation() because
the data read by that call will go into the bit bucket when the section does it standard
call to get the next element.
A similar consideration is in play when the getNext() function is used.

getCachedSchema
result = getCachedSchema(dataService, database, collection, structureFlag, ifNoneThenBuildFlag)
Return the cached schema associated with the given dataService's database collection .
Use a structureFlag of true to return an object as described by getSchemaStructure().
Otherwise a list of properties is returned.
This is based on a point in time sampling and may not map to all objects.
The schema can be redetermined using the various Qarbine querying tools.

cachedSchemas
list = cachedSchemas(dataService database, namesOnly)
Return a list of cached schemas for the given data service.
The default names only is false.

getSchemaAnalysis
result = getSchemaAnalysis(dataService, database, collection, structureFlag)
Return the schema associated with the given dataService's database collection.
If the database is null or empty, then any database value of the dataService is used.
The results are cached in the catalog.
Use a structureFlag of true to return an object as described by getSchemaStructure().
Otherwise a list of properties is returned.
This is based on a point in time sampling and may not map to all objects.
The schema can be redetermined using the various Qarbine querying tools.

getSchemaStructure
result = getSchemaStructure(cachedSchema, structureFlag)
Return an object with fields and data types based on the Qarbine cached schema information.
This is based on a point in time sampling and may not map to all objects.
The schema can be redetermined using the various Qarbine querying tools.

getDistinctValues
list = getDistinctValues(dataService, database, collection, fieldPath [,howMany] )
Return the distinct values when using the data service and other parameters.

hasMoreData
boolean = hasMoreData()
Return whether there is more data in the active data retrieval.

getMetadata
object = getMetadata()
Get any metadata from the active data source.
With the GremlinDriver, if there are any gremlin path hints then always create the nodes and links in the metadata.
The metadata also has pathHints, and pathHintsActions.

getMetadataNodesAndLinks
list[nodes, links] = getMetadataNodesAndLinks(asIsFlag)
Get any nodes and links content from the the the active data source's metadata.
With the GremlinDriver, if there are any gremlin path hints then always create the nodes and links data.
For example, %%gremlin v, inv, outv, ine, e ...
If asIsFlag is false then nodes are just strings and links have from and to fields.
The result is alist of nodes with name field and a list of links with source, target, and name fields.

newDataReader
reader = newDataReader(aList)
return a data reader for the list.

nullValue
value = nullValue()
Return the null value.

resource
content = resource(name [, includeContent] )
Return the named resource.

resourceAsImage
content = resourceAsImage(imageResourceName)
Return the named image oriented resource.

undefinedValue
value = undefinedValue()
Return the undefined value.

usageStatistics
object = usageStatistics(startDate, endDate)
Return usage statistics between the given dates.

trialStatistics
object = trialStatistics( )
Return trial statistics.

Data Type

clazz
string = clazz(value)
Return name of class or type of object.
See also objectClass(value)

ifError
value = ifError(expression, alternateValue)
Return the alternate value is the expression evaluates to an error value.

isBoolean
boolean = isBoolean(value [, value n] )
Return true if the value is a boolean.

isDate
boolean = isDate(value [, value n] )
Return true if the values are all dates.

isDecimal
boolean = isDecimal(value [, value n] )
Return true if the values are all Decimal objects.

isDocument
boolean = isDocument(value)
Return true if the values are all of type object.

isNotNull
boolean = isNotNull(value [,value n] )
Return true if the value(s) are all not null. This is a soft comparison.

isNull
boolean = isNull(value [,value n] )
Return true if the values are null. This is a soft comparison.

isList
boolean = isList(value [,value n] )
Return true if the value is an array list.

isNumber
boolean = isNumber(value [,value n] )
Return true if the values are numeric in nature.

isObject
boolean = isObject(value)
Return true if the values are of type object.

isString
boolean = isString(value)
Return true if the values are of type string.

typeOf
string = typeOf(value)
Return the value's type. An Array's type is 'list'.

Summary Aggregates

avg
value = avg(expression [,triggerExpression [, resetExpression] ] ) postExpression
Perform an average aggregate calculation.

collect
list = collect(expression [,triggerExpression [, resetExpression] ] ) postExpression
Perform an collecting aggregate calculation.

count
value = count(expression [,triggerExpression [, resetExpression] ] ) postExpression
Perform a count aggregate calculation.

distinctCount
list = distinctCount(expression [,triggerExpression [, resetExpression] ] ) postExpression
Perform a distinct count aggregate calculation.

distinctCountOf
list = distinctCountOf(expression [,triggerExpression [, resetExpression] ] ) postExpression
Perform a distinct count of a particular value aggregate calculation.

first
value = first(expression [,triggerExpression [, resetExpression] ] ) postExpression
Perform a first aggregate calculation.

last
value = last(expression [,triggerExpression [, resetExpression] ] ) postExpression
Perform a last aggregate calculation.

max
value = max(value1, value2)
Perform a maximum aggregate calculation.

min
value = min(value1, value2)
Perform a minimum aggregate calculation.

median
value = median(expression [,triggerExpression [, resetExpression] ] ) postExpression
Perform a media aggregate calculation.

mode
value = mode(expression [,triggerExpression [, resetExpression] ] ) postExpression
Perform a mode aggregate calculation.

sum
value = sum(expression [,triggerExpression [, resetExpression] ] ) postExpression
Perform a summation aggregate calculation.

stDev
value = stDev(expression [,triggerExpression [, resetExpression] ] ) postExpression
Perform a standard deviation aggregate calculation.

stDevP
value = stDevP(expression [,triggerExpression [, resetExpression] ] ) postExpression
Perform a standard deviation of population aggregate calculation.

var
value = var(expression [,triggerExpression [, resetExpression] ] ) postExpression
Perform a variance aggregate calculation.

varP
value = varP(expression [,triggerExpression [, resetExpression] ] ) postExpression
Perform a variance of population aggregate calculation.

range
minMaxList = range(expression [,triggerExpression [, resetExpression] ] ) postExpression
Perform a range aggregate calculation.

trackMaxElement
dataObject = trackMaxElement(expression [,triggerExpression [, resetExpression] ] ) postExpression
Perform a maximum value aggregate calculation.

trackMinElement
dataObject = trackMinElement(expression [,triggerExpression [, resetExpression] ] ) postExpression
Perform a minimum value aggregate calculation.

List and Table

listAt
value = listAt(list, first|end|last|indexBaseOne, ifNoneValue)
Return the element at the given index or alias (first, end, or last) position.

listAddElement
element = listAddElement(listOrVariableName, expr1 [,expr n] )
Add an element to an already defined list.
Return the last added element.

listAdd
list = listAdd(listOrVariableName, expr1 [,expr n] )
Add an element to a list. Create the list if null.

listAppendList
newList = listAppendList(list1OrVariableName [,list n] )
Add a list of elements to a list.

listAppendValueToBuffer
list = listAppendValueToBuffer(listOrVariableName, expr1 [,expr n - 1], atMostCount)
Add elements to a list and insure the size is not exceeded.

listDistinctValues
list = listDistinctValues(list, field, sortDirection)
Return the distinct values in the list for the given field.

listFromField
list = listFromField(sourceList, elementFieldPath [,ifNullValue] )
Return a list from the source object field path.
If any value is null along the way return errorValue().
The elementFieldPath may be a dotted path.

listFromListElementExpression
list = listFromListElementExpression(sourceList, elementExpression)
Gather a list of values by evaluating the elementExpression for each element in the sourceList.

listFromString
list = listFromString(csv | json | tsv, string, [firstHasFieldNames] )
Return a list by parsing elements in the string.
For CSV and TSV, indicate if the first line contains the field names. The default is false.
If so then the list of complex objects is returned.
If not then the string itself is the list of simple values.
For JSON if the value is not a string then a list with one element is returned.
Use jsonWithPrefix in situations such as when AI may return text with literally a 'json' string prefix.

insureList
list = insureList(value)
If the value is not an array, then return an array with that element.
Useful when passing in variables. Below is an example with @type
db.animals.find( {type: {$in : [! insureList(@type) !] } } ).sort( {type:1} )

listWith
list = listWith(expr 1 [,expr n] )
Return a list with the given values.

listWithSequence
list = listWithSequence(fromNumber, toNumber [, delta] )
Return a list with values across the given sequence.

idListWith
list = idListWith(valueFieldName, [id 1, value 1...] )
Create a list with elements containing an ID field and a valueFieldName field.
The first element's key is argument 1 and its value argument 2, etc.
Useful for creating lookup tables.

listFromLists
list = listFromLists(list 1 [,list ... , list4] )
Combine multiple lists into a single list. A null value is deemed an empty list.
This returns a list so that it can be used for a group data formula
such as 'listFromLists(@moviesElement.writers, @moviesElement.actors)'.
The elements of the single list have fields of elm1, elm2, elm3, and elm4..
The values are the corresponding elements form the input lists.
Useful for multi-column group across multiple lists (movies.writers, movies.actors).

listSlice
listOfElements = listSlice(list, howManyPieces, formatByRows)
The given list is sliced into the given number of sublists.
The Sublists are then merged into a list with up to howManyPieces elements per row.
Access each pieces with field alias of the format 'elm##' (elm1, elm2, etc.).
A list with 7 elements sliced 3 ways returns a new list.
The rows 1 through 2 have 3 values each and the 4th 1.
A formatByRows of true means values flow across the rows and downward.
A false means format by columns (down and then across).

listReduce
list = listReduce(sourceList, keyFieldList [, valueFieldList] )
If keyFieldList is empty then just create a new list with the subset of fields.

listRemoveNulls
list = listRemoveNulls(list)
Return the list with any nulls removed from the list argument.

listRemoveValues
list = listRemoveValues(list, matchValue [,matchValue n] )
Return the list with any match values removed from the list argument.
If the 2nd argument is a list, then it is used as arguments 2..n!

listAvg
number = listAvg(objectArray [, elementFieldPath] )
Return the average of the list.

listMax
number = listMax(objectArray [, elementFieldPath] )
Return the maximum value in the list.

listMin
number = listMin(objectArray [, elementFieldPath] )
Return the minimum value in the list.

listMedian
number = listMedian(objectArray [, elementFieldPath] )
Return the median value in the list.

listMode
number = listMode(objectArray [, elementFieldPath] )
Return the mode value in the list.

listStDev
number = listStDev(objectArray [, elementFieldPath] )
Return the standard deviation of the list.

listStDevP
number = listStDevP(objectArray [, elementFieldPath] )
Return the standard deviation of the population of the list.

listVar
number = listVar(objectArray [, elementFieldPath] )
Return the variance of the list.

listVarP
number = listVarP(objectArray [, elementFieldPath] )
Return the variance of the population of the list.

listSize
list = listSize(aList)
Return the size of the list.

listSum
number = listSum(objectArray [, elementFieldPath] )
Return the summation of the list.

listSum
number = listSum(objectArray, elementFieldPath, matchValue)
Return the count of the matching values in the list.

listContains
boolean = listContains(list, value|list [,startingIndexBaseOne] )
Return if the list contains the value.
If the value argument is also a list then iterate through those elements for the comparison.
The comparisons are simple values such as strings and numbers.

listIndexOf
integer = listIndexOf(list, value [,startingIndexBaseOne] )
Return the index of the value in the list (base one).

listLastIndexOf
integer = listLastIndexOf(list, value [,startingIndexBaseOne] )
Return the last index of the value in the list (base one).

createFilter
filter = createFilter(propertyType, propertyName, simpleClause)
Create a filter object which can be used by listFiltered(...).

listFiltered
list = listFiltered(list, filter[1..n])
Filter elements are created via createFilter(...)

listReverse
newList = listReverse(list)
Reverse the elements of the list.

listShallowCopy
list = listShallowCopy(list)
Return a copy of the list.

listToParagraph
string = listToParagraph(aList, sortKeys, elementFieldsList, skipFieldsList, lineBetweenElements, treatAsKeyValuePair, maxElements)
Convert the elements in the list to a text oriented paragraph.
The default sortKeys is false.
Optionally sort them in a case insensitive order.
The default elementFieldsList is an empty list. A '*' means all fields as well.
The default skipFieldsList is an empty list.
The default lineBetweenElements is false.
The default treatAsKeyValuePair is false. Use true if the elements have 2 fields such as a key and a value and you only their their respective values.
The maxElements indicates the most number of elements to convert. 100 is the default.

listSort
list = listSort(list, elementFieldName, [asc|desc|ascIns|descIns], dataType)
Sort the list using the parameters provided.
This is an IN-PLACE sort. Consider listShallowCopy().
The default dataType is 'string' and sort is case sensitive ascending.
ascIns is for ascending with case insensitivity and descIns for descending with case insensitivity.

listTruncate
list = listTruncate(aList, maxSize)
Truncate the list if it exceeds the maximum size.

listTrackerUsing
tracker = listTrackerUsing(keyField(s) [, onlyCareAboutValues, trackOtherValuesFlag] )
Create a tracker which is then used within an inner group or body via listTrackerAdd(...).
You can specify which values you want to track and whether to also track values not in the explicit list.
At a group summary or another group you can then iterate through the tracked values via listTrackerAsList(...).
Compound keys result in values separated by commas. Basically CSV format.

listTrackerAdd
boolean = listTrackerAdd(@tracker, object)
Return true if all of the key fields were not null.
Return false if all of the key fields were not null.

listTrackerAsList
list = listTrackerAsList(@tracker)
Return a list with elements of {key: key's value, list: list of objects with that key}

rangeCompare
value = rangeCompare(value, operator, value1, test1, value2, test2, n...)
value = rangeCompare(rating, '<', "red", 5, "blue", 7, "green", 10.1)
Return red if the rating is less than 5.
Return blue if the rating is less than 7.
Return green if the rating is less than 10.1. So that a 10 is still green.

firstNotNullValue
value = firstNotNullValue(expr1, expr2, ...)
Returns the FIRST non null value.
If none then returns error value.

lastNotNullValue
value = lastNotNullValue(expr1, expr2, ...)
Returns the LAST non null value
If none then returns error value.

rangeTest
integer = rangeTest(value, test1, test2, test n...)
If value < test1 return 0.
If value < test2 return 1.
If value < test3 return 2.
Otherwise returns argument count.

createLookupTableWith
table = createLookupTableWith(key1, value1 [, key2, value2 ...])
Create a lookup table using the provided key\value pairs.

createLookupTable
table = createLookupTable(sourceList, keyField, valueField [,keyPrefix] )
Create a lookup table from the list using the associated key and value field.
Can use the table with lookupTableAdd() and lookupTableAt().

lookupTableAdd
value = lookupTableAdd(sourceTable, object, keyValueOrNull, valueOrNull)
Add a lookup entry based on an object.
If the keyValueOrNull is null then the lookup table's default key field of the object is used as the key.
If the valueOrNull is null then the lookup table's default value field of the object is used as the value.
The object may be a list of objects to add as well. If so any key and value arguments are element field names.

createDbLookupTable
table = createDbLookupTable( dataService, database, collection, keyField, csvValueFields, ifNotFoundValue, readNow, isSingleFieldObject)
Create a lookup table from running a query using the given data service.
Use the table variable argument in lookupTableAsList().
The initial elements loaded are truncated to 1000 elements.

lookupTableAsList
list = lookupTableAsList(sourceTable [, sortByField [, haveStrings] ] )
Create a list from the contents of the lookup table.

dbLookupTableAt
value = dbLookupTableAt(tableVariable, keyValue, ifNotFoundValue)
Lookup a value in the given lookup table. Perform a remote query as necessary.

lookupTableAt
value = lookupTableAt( table, key, ifNotFound)
Lookup a value in the given lookup table.

xyr
xyrDocument = xyr(x, y [, z] )
Crate an object with x, y, and z fields.
Convenience method for charting with x, y, and radius.

cjsElement
document = cjsElement(x, y [ , v [, r [, c [, t] ] )
Chart.js data set data element builder.
Creates a document with well known fields
x Used for X coordinate
y Used for Y coordinate
v Used for label
r Used for radius (in pixels) for bubble charts only
c Used for background color in dataset.backgroundColor()
t Used for text color in plugins.datalabels()

Document and JSON

keyValuesAsParagraph
string = keyValuesAsParagraph(keyValuesList [, sortKeys [, skipKeyList [, uncamelizeOption] ] ])
Iterate through the key\value list and create a paragraph.
The key label may be uncamelized based on the uncamelizeFlag value.
Optionally sort them in a case insensitive order.

documentFieldNames
list = documentFieldNames(object [, sortKeys] )
Return the field names of the given object.
Optionally sort them in a case insensitive order.

documentFieldsToList
list = documentFieldsToList(object [sortKeys [, elementFields] ] )
Return the field values of the object for the given field names.
Optionally sort them in a case insensitive order.

subdocument
object = subdocument(object [, sortKeys, [elementFieldsList [,skipFieldsList] ] ])
Create a new object from the given object's specified fields.

documentToParagraph
string = documentToParagraph(object [, sortKeys, [elementFieldsList [,skipFieldsList] ] ])
Return a string with a line for each key\value pair of the document.

documentToKeyValues
keyValuesList = documentToKeyValues(object [, sortKeys [, skipNulls] ] )
Return a list of elements with key and its value the given object.
i.e., [ {_id : x, version: "4.0"}, ...]
Optionally sort them in a case insensitive order.

documentProperty2
value = documentProperty2(object, dottedPathOrList [,ifNullValue, [,ignoreDots] ] )
Return the value of the field indicated by the dotted path string.
Optionally pass in the path as a list of fields.
If the object is null then just return null.
If any value in the path is null or an empty string then return ifNullValue.

documentProperty
value = documentProperty(object, dottedPathOrList [,ifNullValue, [,ignoreDots] ] )
Return the value of the field indicated by the dotted path string.
Optionally pass in the path as a list of fields.
If the object is null then just return null.
If any value in the path is null or an empty string then return ifNullValue.

prop
value = prop(object, dottedPath [,ignoreDots])
A short alias for documentProperty().

jsonParse
value = jsonParse(string)
Create an object from a JSON string.

convertJsonFields
value = convertJsonFields(source, jsonField1 [,jsonField n] )
For each JSON field name, parse it and set it back into the source.
Use this when databases return the JSON string itself instead of the expanded JSON object.
Return the source as the result.

documentFromJson
document = documentFromJson(string)
The is the same as jsonParse(string)

documentAdd
document = documentAdd(targetDocument, document 1 [,document n] )
Add or replace fields in the target document with those from the other documents.
Return the result.

documentAddKeyValues
object = documentAddKeyValues(object, keyPath, value [, keyPath_n, value_n] }
Set the field values(s) of the object.
The setting of values is done in-place. No copying of the object is done.
The recommended style is
foo = documentAddKeyValues(@foo, 'key', 1234)
This creates the 'foo' variable object on the fly if it is currently null.
To add multiple values to a single object use
foo = documentAddKeyValues(@foo, 'key1', 1, 'key2', 2, ...)

mergeNodesAndLinks
nodesAndLinks = mergeNodesAndLinks(lookUp1, links)
nodesAndLinks = mergeNodesAndLinks(lookUp1, links, nodeIdField, linkSourceField, linkTargetField, nodeNameFields, excludeLabel)
nodesAndLinks = mergeNodesAndLinks(lookUp1, lookUp2, links, nodeIdField, linkSourceField, linkTargetField, nodeNameFields, excludeLabel)
The lookup arguments are objects created by createLookupTable(). There may be 1 or 2 of them.
The links argument is an array.
These 3 *Field arguments are strings.
The default linkSourceField is 'source'.
The default linkTargetField is 'target'.
The nodeNameFields is a prioritized list of field names for node names used in the diagram display.
The excludeLabel indicates to exclude the node Label in its displayed value.
Create an object with nodes and links fields.
If nodeIdField is set then the source/target values map to that field within the node.

documentFromKeyValues
object = documentFromKeyValues(field1, value1 [field n, value n] )
Crate an object using the given keys and values.

setVariablesFromDocument
boolean = setVariablesFromDocument(object1, [object n] )
For each object, iterate over its keys and assign variables
based on those key names the value of the corresponding field.

setVariable
value = setVariable(name, value)
Set the variable named by the first argument to the 2nd argument value.

Conversion and Formatting

treeIndent
string = treeIndent(howManyLevels, text [, ifNoText [, useSlash] ] )
Return a string with indentation prefix characters.

convert
value = convert(fromType, toType, value, ifNullValue)
fromType: string, number, base64
string -> date, number, integer, boolean
hex -> integer
number -> string, date
base64 -> binary
array -> base64

json
string = json(data [, suppressNulls , spacing] )
The true|false argument is whether to suppress nulls.
The spacing value is per JSON.stringify API argument.

formatByteSize
string = formatByteSize(number, separator)
Format a number using bytes, KB, MG, GB, or TB.

formatHertz
string = formatHertz(number, separator)
Format a number using Hz, KHz, MHz, GHz, or THz.

format
string = format(data, dataType, template, options...)
dataType options: List, Boolean, CSV, Date, DateRange, DateOnly, JSON, Number, Suppress, Time
DateRange templates: 'days', 'weeks', 'months', or 'years' plus optional 'asInteger'.

numberOrZero
value = numberOrZero(value)
Return the number or 0 if the value is not a number.

stringFormat
string = stringFormat(template, value 1, value n)
Replace {number} placeholders in the template with the corresponding argument.

str
string = str(value)
Return the string version of the object.

stringMask
string = stringMask(mask, value [,rightToLeft] )
See https://www.npmjs.com/package/string-mask.
0 Any numbers
9 Any numbers (Optional)

Any numbers (recursive)

A Any alphanumeric character
a Any alphanumeric character (Optional) Not implemented yet
S Any letter
U Any letter (All lower case character will be mapped to uppercase)
L Any letter (All upper case character will be mapped to lowercase)
$ Escape character, used to escape any of the special formatting characters.

Color Support

colorRange
rgbList = colorRange(howMany)
Generate a range of RGB colors for the given number of colors.

complementaryTextColor
rgbColor = complementaryTextColor(aNameOrHexRGB)
If the argument is a dark color then return white, otherwise return black.

defaultColor
value = defaultColor()
The content presenters know the defaults based on foreground or background context.

tohex
XX = tohex(number, padToDigits)
return the number as a hexidecimal string.
Optional padToDigits to prefix with '0' to that many total digits.

heatMapColor
#rrggbb = heatMapColor(low, high, value, colorRange, softColors)
Converts the value into a heat map color.
The color ranges are redToBlue, blueToRed, yellowToBlue.
The redToBlue color range is the default.
Use softColors of true for less intense colors.

hexColor
#rrggbbaa = hexColor(r, g, b, [a] )
Returns the hex color representation for the RGB values.

isColorDark
boolean = isColorDark(aNameOrHexRGB)
Is the color a dark one?

isColorLight
boolean = isColorLight(aNameOrHexRGB)
Is the color a light one?

normalizeColor
value = normalizeColor(aName)
Normalize the color name.
Allow 'Lemon Chiffon' to be 'lemonchiffon' so that downstream things match up.

rgb
rgbString = rgb(r, g, b)
Returns the string rgb(#,#,#).
Used by Chart.js world for color formulas.
See also hexColor() below.

rgba
rgbaString = rgba(r, g, b, a)
Returns the string rgba(#, #, #, #)
Used by Chart.js world for color formulas.
See also hexColor() below.

zebraColor
color = zebraColor( [oddColor] )
Return alternating colors based on data retrieval element fetching.
Shorthand for, if (isOdd(@objectNumber), '#ffecb3bf', null)

Image Support

classImage
imagePath = classImage(className [,wantTransparent] )
imagePath = classImage(object [,wantTransparent] )
Return the image path for the component class name.

commonImage
imagePath = commonImage(alias, optionalArgument)
Return the image path for the alias.
Recognized names: askAI, error, help, folder, link, privateFolder, qarbine, www, and vendor+arg1.
Recognized actions: edit, open, view, and run.
Data type images are also available.
Array, binary, boolean, Color, Document, Date, dateOnly, time
bigint, BigDecimal, BigNumber, Decimal, Decimal128, $numberDecimal,
$numberDouble, $numberInt, $numberLong, $date, Integer, Map
Number, Object, ObjectId, string, symbol.

objectClass
imagePath = objectClass(objectOrReference)
Return the class of the Qarbine component object.

objectClassIs
boolean = objectClassIs(objectOrReferenceClass, 'isSomeResultClass' | 'isRunnableClass' | 'isReferenceableClass' )
Return the status of the Qarbine component object. The argument is case sensitive.
Aliases for the 2nd argument are SomeResult, Runnable, and Referenceable.

objectClassImage
imagePath = objectClassImage(clazz | Object | Reference)
Return the image path for the Qarbine component's class

objectActionImage
imagePath = objectActionImage( 'open' | 'run' | 'edit' | 'copyReference' )
Return the image path for the Qarbine action.

Template Processing

activeLevel
string = activeLevel()
Return the active template level.
For example '1.1'.

activeSection
string = activeSection()
Return the active template section.
For example: PH, RH, GH, body, GS, RS, PF.

activeLocation
string = activeLocation()
Return the active template location.

cancelReport
cancelReport(cancelMessage)
Proactively cancel the template running.

currentLineNumber
integer = currentLineNumber()
Return the current output line number.

currentPageNumber
integer = currentPageNumber()
Return the current output page number.

nextCell
string = nextCell()
Return the next output cell alias.
Used by some custom cells which interact based on the next or previous cell.

previousCell
string = previousCell()
Return the previous output cell alias.
Used by some custom cells which interact based on the next or previous cell.

doingPageBreak
boolean = doingPageBreak()
Return whether a page break is active.

doingLastPageFooter
boolean = doingLastPageFooter()
Return whether processing the last page footer.

onLevel
boolean = onLevel(aLevel)
Return whether processing is on the given level.

onBody
boolean = onBody([level])
Return whether processing is on a section.

onGroupHeader
boolean = onGroupHeader(level)
Return whether processing is on a group header section.

onGroupSummary
boolean = onGroupSummary(level)
Return whether processing is on a group summary section.

onReportHeader
boolean = onReportHeader()
Return whether processing is on the report header section.

onReportSummary
boolean = onReportSummary()
Return whether processing is on the report summary section.

pointsLeftOnPage
integer = pointsLeftOnPage()
Return how many points are left on the current page.

pointPosition
integer = pointPosition()
Return the current page location in points.

pageCannotFitLines
boolean = pageCannotFitLines(howManyLines [,pointSize] )
Return whether the current page can NOT fit the additional number of lines.
Basically the inverse of canPageFitLines().
Convenient to use for page breaking in a positive function manner.

canPageFitLines
boolean = canPageFitLines(howManyLines [,pointSize] )
Return whether the current page can NOT fit the additional number of lines.
Assumes 14 points per line.

canPageFitPoints
boolean = canPageFitPoints(numberOfPoints)
Return whether the current page can fit the given number of points.

pageCannotFitPoints
boolean = pageCannotFitPoints(numberOfPoints)
Basically the inverse of canPageFitPoints().
Convenient to use for page breaking in a positive function manner.

skipRestOfSection
boolean = skipRestOfSection(boolean [,boolean...])
If any boolean is true then skip over the rest of the lines of the current section.
This avoids having to place process conditions on subsequent section lines.

finalPageNumber
value = finalPageNumber(originalPageNumber)
Return the total number of pages.

annotateResult
status = annotateResult(key1, value1, etc. )
Add an annotation to the result component.

pot
number = pot(thisValue, totalValue)
Perform a percent of total calculation.
The first expression value is obtained during the first pass.
That value is then divided by the total value variable reference when the latter is set.

twoPass
value = twoPass(pass1Expression, pass2Expression) postExpression
Perform a two pass calculation using at least the first 2 expressions.
The first expression value is obtained during the first pass.
After the first pass completes, the second expression is evaluated with @cellValue being available.
That result is then evaluated with any optional post expression.
The cell suppress and color expressions may reference @pass1Value and @cellValue values.

triggerFormula
[result] = triggerFormula(baseExpression, triggerKey, finalExpression)
Perform baseExpression and save the result for later.
When the section triggerKey completes such as '1.1 GS' then
evaluate finalExpression with @cellValue as the baseExpression result.
The cell suppress and color expressions may reference @pass1Value and @cellValue values.

Matrix

matrixAvg
number = matrixAvg(lineContextValue, condition, value, ifNoneValue)
Determine the average of data retrieval elements while the condition is true.

matrixContext
string = matrixContext(label, lineContextValue)
Set the matrix evaluation context for other matrix macros.
Consider a company name of Foo and using the ID as the context value.

matrixCount
integer = matrixCount(lineContextValue, condition, value, ifNoneValue)
Determine the count of data retrieval elements while the condition is true.

matrixLastCount
integer = matrixLastCount()
How many objects were traversed performing the last matrix cell operation?

matrixMax
number = matrixMax(lineContextValue, condition, value, ifNoneValue)
Determine the maximum value of data retrieval elements while the condition is true.

matrixMin
number = matrixMin(lineContextValue, condition, value, ifNoneValue)
Determine the minimum value of data retrieval elements while the condition is true.

matrixSum
number = matrixSum(lineContextValue, condition, value, ifNoneValue)
Determine the sum of data retrieval elements while the condition is true.

User Profile

signedOnAccount
string = signedOnAccount()
Return the signed on user account.

signedOnTags
list = signedOnTags()
Return the sign on account's tags as set by the Qarbine administrator.

User Interaction

call
value = call( receiver, functionName [, argument...] )
Perform a function call in the receiver object.

componentObjectAction
action = componentObjectAction([run,open, edit, copyReference], catalogObject, key1, value1, key2, value2...)
Create an action object. Used by cell pop up menus and custom button cells.

componentAction
action = componentAction([run|open], class, fullFolderPath, name, key1, value1, key2, value2...)
Create an action object. Used by cell pop up menus and custom button cells.
Mainly a drill down support function
Argument options for class, fullFolderPath, name
Cases

fullReference, null, null
This is useful when pasting in component reference links.
clazz, restOfReference, null
clazz, fullPath, name

componentOpen
action = componentOpen(componentObjectOrReference)
Return an action that when invoked opens a component editor.

dialogAction
action = dialogAction(title, key1, value1, ...)
Return an action that when invoked opens a dialog.
Mainly a drill down support function.

javaScriptAction
action = javaScriptAction(targetObjectName, fnName, value1, value2...)
Return an action that when invoked performs the JavaScript action.
Mainly a drill down support function.
For targetObject, use 'window' for the window object.
"tool" is our well known global.
All arguments MUST be JSON'able.

googleAction
action = googleAction(what [, areaName])
See CustomCellGoogleSearchRenderer for details.

weatherForecastAction
action = weatherForecastAction(longitude, latitude)
Return an action that when invoked opens forecast.weather.gov for the given coordinates.

urlAction
action = urlAction(URL, Get|Post, key1, value1, key2, value2...)
action = urlAction(URL, Get|Post, encodedString)
action = urlAction(URL, Get|Post, concat(...) )
Return an action that when invoked opens the URL.
Mainly a drill down support function.
ALWAYS opening another tab via target=_blank.
key1 starts at zero base index 2

askAiAction
action = askAiAction(text, aiAlias, options)
The first argument is the prompt fully formed. Consider using concat(...) operations.
Use an AI alias of 'explain' to only see what would have been sent to the backend AI service.
This is basically a completion() call for a menu option.

askAiPromptAction
action = askAiPromptAction(fullComponentPath, aiAlias, options, variableName1, value1, variableName2, value2...)
Use this for menu options.
The parameters are the same as for askAiPrompt().

askAiPrompt
action = askAiPrompt(pathToFileWithPrompt, aiAlias, options, variableName1, value1, variableName2, value2...)
Provide the path to the File in the catalog containing the prompt text.
It may have [! ... !] expressions or basic variable references @varName.
The variables are provided in order to fill in these placeholders.
The variableName of 'error' is reserved.
The 'reportContent', 'reportSummaryContent' and 'groupContent' are special value references and are
dynamically set to the TSV text for consumption by any File component placeholders.
Use an AI alias of 'explain' to only see what would have been sent to the backend AI service.

Geographic

addressLocation
{long, lat} = addressLocation(streetAddress)
Return coordinates by looking up the given address.

countryCode3To2
code2 = countryCode3To2(code3)
Decode the 3 character country code into the 2 character country code.

countryCode2ToName
name = countryCode2ToName(countryCode2)
Decode the 2 character country code into the country name.

countryCode2ToName
name = countryCode2ToName(countryCode3)
Decode the 3 character country code into the country name.

countryNameToCode2
code2 = countryNameToCode2(properName)
Encode the country name to its 2 character country code.

countryNameToCode3
code3 = countryNameToCode3(properName)
Encode the country name to its 3 character country code.

geoDistance
distance = geoDistance(fromLatLong, toLatLong [, 'meters'] )
Return the geographic distance between the two coordinates.
Here is an example using names, geoDistance( addressLocation("Boston") , addressLocation("New York") )
The default is kilometers.

ipLocation
about = ipLocation(IPAddress [,granularity] )
Return a geo coordinate for the IP address.
The optional 2nd argument indicates the level of granularity.
It can be ASN, city, or country. The default is city.
Use loc = ipLocation('1.2.3.4') and then have other cells grab from the loc variable.

whoIs
{stuff} = whoIs(host)
Perform an ICANN lookup for the given host.

dnsResolve
ipAddress = dnsResolve(host)
Perform a DNS resolution of the name to an IP address relative to the Qarbine server.

metersToMiles
number = metersToMiles(number)
Convert meters to miles.

milesToMeters
number = milesToMeters(number)
Convert miles to meters.

multipolygonFromGeoJsonHolder
holder = multipolygonFromGeoJsonHolder(geoJsonPolygon, label)
Create a polygon holder.

mapPolygon
shape = mapPolygon(listOfLongLat [, lineColor [, fillColor [, tooltip] ] ] )
Create a polygon shape from the given coordinates and properties.

mapPolyline
shape = mapPolyline(listOfLongLat [, lineColor [, fillColor [, tooltip] ] ] )
Create a polyline shape from the given coordinates and properties.

mapCircle
shape = mapCircle(centerLongLat, radiusInMeters [, lineColor [, fillColor [, tooltip] ] ] )
Create a circle shape from the given coordinates and properties.

mapRectangle
shape = mapRectangle(upperLeft, lowerRight [, lineColor [, fillColor [, tooltip] ] ] )
Create a rectangle shape from the given coordinates and properties.

pointFromGeoJsonHolder
{long, lat [,label] } = pointFromGeoJsonHolder(geoObject [, label])
Create a simple coordinate object.
The first argument is expected to have a field coordinates with [long, lat]
An empty or null label results in no label.

pointForCity
{long, lat} = pointForCity(commonCityName [,useLongFieldNames] )
Return a simple coordinate object for some well known cities.
Berlin, Madrid, London, Paris, Rome, Seattle, San Fran, Sydney, Tokyo, New York.

pointFromDocument
{long, lat} = pointFromDocument(longitudeLatitudeDocument [,label] )
Create a simple coordinate object from the larger object.

pointFromLongLat
{long, lat} = pointFromLongLat(long, lat [,label] )
Create a simple coordinate object for the given coordinates.

Web

webObservedIpAddress
string = webObservedIpAddress()
Return the web observed address of the host running this macro.

basicAuthValue
result = basicAuthValue(user, password)
Return 'Basic user:password'
Use this to get an authorization argument for ajax calls. i.e. btoa(username + ":" + password)

encodeArguments
result = encodeArguments(key1, value1, key n, value n)
Return a string with key=encodedValue&...
Any null is passed as an empty string.

doRest
result = doRest(url, method, encodedArgs, replyDataType, authorization, cookie, returnReplyField)
Perform a REST interaction with the given properties.

doServiceRequest
result = doServiceRequest(restfulURL, returnType)
Perform a Qarbine request using the given URL.
The returnType can be string, base64, binary, or json. The default is string. This is case sensitive.

imdbImagePath
string = imdbImagePath(string)
Return the image path from the IMDB encoded URL.

makeHTTPS
result = makeHTTPS(url)
If the URL starts with http:, then change it to start with https:

urlParameter
string = urlParameter(aValue)
Encode the argument as a URL parameter.

Catalog

catalogContents
dataOrMsg = catalogContents(fullFolderPath, clazz , extension, tags, [folders|components|both], computeChecksums )
Return catalog contents for the specified constraints.
May use 'mine' as fullFolderPath argument for the user's private folder.

catalogFolders
folderNames = catalogFolders()
Return the root catalog folders.
List elements have {id, inFolderCollection, name (nice name), u (usage), p (permission), isRoot, _class (Folder) }

catalogFolderPath
string = catalogFolderPath(inFolderCollection, inPath)
Format a catalog folder path.

catalogFolderObjectPath
string = catalogFolderObjectPath(folderObject [, makeNice] )
Format a catalog folder path.
Extract the name of the collection from the folder object.

catalogFolderCollection
string = catalogFolderCollection(folderObject [, makeNice] )
Extract the name of the root folder from the folder object.

catalogFolderDatabase
string = catalogFolderDatabase(folderObject [, makeNice] )
Extract the name of the database from the folder object.

catalogFolderTags
tags = catalogFolderTags(dbAndCollection)
Return the list of unique tags within the given catalog root folder (AKA collection).

catalogObject
object = catalogObject(object)
Return a smart Qarbine component from the simple object structure.

catalogStatistics
statsList = catalogStatistics()
These stats are collection summary counts for each class.

catalogModifiedByStatistics
statsList = catalogModifiedByStatistics(modifiedBy, class, database, collection, inPath, flatten)
These stats are modifiedBy and class counts with optional filtering.

concurrentUsers
entries = concurrentUsers()
These entries are volatile based on user and client IP activity.

customCells
list = customCells()
Return stub instances of all the custom cells.
Use customCellNames() for just a list of names.
A formula can use = @current.getRegistrationName() to obtain the name.
The getAboutFormula() is also useful.

dataSetData
data = dataSetData(fullStoragePath | folderPath, name)
dataSetData("q_catalog|qf_general/foo/bar", "A bunch of animals")
dataSetData("q_catalog|qf_general/foo/bar/A bunch of animals")

fileContent
data = fileContent(fullStoragePath | folderPath, name)
fileContent("q_catalog|qf_general/foo/bar", "A bunch of animals.txt")
fileContent("q_catalog|qf_general/foo/bar/A bunch of animals.txt")

reportTemplatesWithCustomCell
folderObjects = reportTemplatesWithCustomCell(listOfNames)
Return components which reference the custom cell names.

promptsUsing
folderObjects = promptsUsing(listOfCustomPromptClassNames)
Return components which reference the custom prompt class names.

dataSourcesWithText
folderObjects = dataSourcesWithText(findText)
Return data source components which reference the text.

folderPath
string = folderPath(folderEntry)
The path to a root entry is the entry's ID => AKA inFolderCollection
The path to a folder includes the folder's name => inFolderCollection | inPath | name
The path to a component => inFolderCollection | inPath

fullFolderPath
string = fullFolderPath(folderPath, name)
string = fullFolderPath(fullStoragePath)
string = fullFolderPath(copyReferenceString)
string = fullFolderPath(object)
This path is just the folder one. It excludes the component name.

fullStoragePath
string = fullStoragePath(folderPath, name)
string = fullStoragePath(fullStoragePath)
string = fullStoragePath(copyReferenceString)
string = fullStoragePath(object)
Return the component's storage path.
This path includes the component name.

storageName
string = storageName(folderPath, name)
string = storageName(fullStoragePath)
string = storageName(copyReferenceString)
string = storageName(object)
Return the component name.

referencesToDataSource
folderObjects = referencesToDataSource(folderPath, name)
Return a list of components which reference the given data source.
folderObjects = referencesToDataSource(fullStoragePath)
folderObjects = referencesToDataSource(copyReferenceString)

referencesToDataSet
folderObjects = referencesToDataSet(folderPath, name)
Return a list of components which reference the given data set.
folderObjects = referencesToDataSet(fullStoragePath)
folderObjects = referencesToDataSet(copyReferenceString)

referencesToPrompt
folderObjects = referencesToPrompt(folderPath, name)
Return a list of components which reference the given prompt.
folderObjects = referencesToPrompt(fullStoragePath)
folderObjects = referencesToPrompt(copyReferenceString)

referencesToResource
folderObjects = referencesToResource(name)
Return a list of components which reference the given resource.

referencesToStyleSet
folderObjects = referencesToStyleSet(folderPath, name)
Return a list of components which reference the given style sheet.
folderObjects = referencesToStyleSet(fullStoragePath)
folderObjects = referencesToStyleSet(copyReferenceString)

resources
names = resources( )
Return all visible Resources minus their content.

catalogClasses
names = catalogClasses(asClazzLabelList)
Return all storable classes. Useful for catalog prompts in top X of what templates.

longClassName
names = longClassName(internalName)
Return long name. 'QBE'-> 'Query by example', 'DataSource' -> 'Data Source'

Configuration

featureLevel
integer = featureLevel( )
Return the installation's feature level.

computeNodes
computeNodes = computeNodes(activeState)
This list is based on the ComputeNode collection.

computeNodeDetails
computeNodes = computeNodeDetails(activeState)
This list is based on the ComputeNode collection.
This returns lots of details of each compute node.

customCellNames
list = customCellNames()
Return the registered custom cell names.

dataExporterNames
list = dataExporterNames()
Return the registered data exporter plugin names.

customFunctionNames
list = customFunctionNames()
Return the registered custom function plugin names.

exportQueryToNames
list = exportQueryToNames()
Return the registered export query plugin names.

modelClassNames
list = modelClassNames()
Return the registered model class names.

reportResultFormatterNames
list = reportResultFormatterNames()
Return the registered result formatter plugin names.

sendToNames
list = sendToNames()
Return the registered sendTo plugin names.

Miscellaneous

aiAssistants
[ {type, alias} ] = aiAssistants( )
Get a basic list of configured AI assistants.

tweakWholeNumbers
vectors = tweakWholeNumbers(numberArray)
Make sure no numbers are integer-like. Nudge them by 0.00000001 to be decimal-like.

embeddings
vectors = embeddings(string, alias, inputType, noWholeNumbers, explainFlag)
Obtain a vectors list for the given string.
The alias specifies which configured service to use. A null means use the default.
The inputTypes are 'text' and 'image' and support varies by your configuration. The default is 'text'.
Use noWholeNumbers of true to avoid any integers being seen downstream. MongoDB has thrown an error in such cases. The values are incremented by 0.00000001.
The options is an object whose fields vary by AI Assistant service.
The explainFlag may be true to return details on the AI service call without actually performing the call.
This is only allowed for Qarbine administrators.

completion
{usage, completionText} = completion(prompt, alias, returnOnlyCompletionText, options)
Obtain a completion document for the given prompt.
The prompt is used as the messages argument in the form of [ {role: "user", content: prompt} ]
The alias specifies which configured service to use. A null means use the default.
Use returnOnlyCompletionText of true for the result to be only the text reply.
The options is an object whose fields vary by AI Assistant service.
The explainFlag may be true to return details on the AI service call without actually performing the call.
This is only allowed for Qarbine administrators.

summarize
string = summarize(prompt, alias)
Using the prompt provided, ask the AI assistant for a summary of the template output.
The final AI prompt format is 'Below is a report. PROMPT. OVERALL_TSV.
This is more of a convenience function.

formattedResult
tsvText = formattedResult(inActiveChunk)
Return tab separated text for the current state of the template result.
When inActiveChunk is false or not provided, the overall current text is returned.
When inActiveChunk is true and the formula is on a group summary then that group's header, inside content, and summary up to the cell are returned.
When inActiveChunk is true and the formula is on a report summary then the report summary up to the cell is returned.

sampleEmbeddings
vectors = sampleEmbeddings( )
Return sample vectors list.
A discussion of a CosmosDB’s vector search example can be found at
https://learn.microsoft.com/en-us/azure/cosmos-db/mongodb/vcore/vector-search
That example only uses 3 dimensions for its vector.

MongoDB

buildUsersAndRolesGraph
nodesAndLinks = buildUsersAndRolesGraph(users, customRoles)
Build a nodes and links structure suitable for graph data presentations.

formatHighlightFragments
string = formatHighlightFragments(highlights [, pre_tags, post_tags] )
Create a marked up string based on the ElasticSearch highlight fragments list.
The default pre_tags is <em> and the post_tags is </em>.

formatHighlights
string = formatHighlights(highlights [,excludePath [,fullText] ] )
Create a marked up string based on the highlights list.

graphLookupOrder
list = graphLookupOrder( graphLookupSet, keyField, linkField [, topDownFlag] )
Create a list based on the hierarchy set.
list = graphLookupOrder( @reportingHierarchy, "name", "reportsTo", false);

dateRangeCriteria
string = dateRangeCriteria(fromDate, toDate [,includeFrom , includeTo] )
Return MongoDB query criteria string for the given date range.
The default is each end is inclusive, $gte fromDate and $lte toDate.

inYearCriteria
MongoDbCriteria = inYearCriteria(yearNumber)
Return MongoDB query criteria string for the given year (inclusive).
May use in
db.sales.find( {storeLocation: [! @store !], saleDate: [! inYearCriteria(@year) !] } )

objectId
objectId = objectId(mongoObjectId)
Return a MongoDB Object ID object for the given ID value.

internalUsers
users = internalUsers( )
Return internal users.

internalRoles
roles = internalRoles( )
Return internal roles.

parseMongoDbExplain
stages = parseMongoDbExplain(object)
Return a list of stages parsed from the MOngoDB explain object.

Other Database Support

intervalAsWords
string = intervalAsWords(anInterval)
Return the temporal interval as words.
The object may have years, months, days, hours, minutes, and seconds fields.

intervalAsSeconds
seconds = intervalAsSeconds(anInterval)
Return the interval as seconds.

timeUuidElements
object = timeUuidElements(timeUuidBase64)
Extract Cassandra time UUID elements {timestamp: , ticks: , clockId: , host: };

othertableLookup
value = othertableLookup(dataService, baseId, table, recordIdArg, includeFields, excludeFields, maxRecords)
Perform a dynamic lookup using the given data service and other parameters.

Macro Language Functions

Total Functions: 460​

General​

Variable​

Logical​

String​

Numeric​

Finance​

Date and Time​

Data​

Data Type​

Summary Aggregates​

List and Table​

Document and JSON​

Conversion and Formatting​

Any numbers (recursive)

Color Support​

Image Support​

Template Processing​

Matrix​

User Profile​

User Interaction​

Geographic​

Web​

Catalog​

Configuration​

Miscellaneous​

MongoDB​

Other Database Support​